<!DOCTYPE html>
<html>
<head>
  <meta charset="utf-8">
  
  
  <title>Hive高级用法 | Hexo</title>
  <meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
  <meta name="description" content="Hive高级用法 实验目的 熟悉hive QL语句实现表的各种连接方式，使用hiveserver2建立hive远程连接。  实验内容 一、表连接 1.表连接（内连接，左外连接，右外连接和全外连接）   hive只支持等值连接，即ON子句中使用等号连接，不支持非等值连接。 如果连接语句中有WHERE子句，会先执行JOIN子句，再执行WHERE子句。 可以JOIN多个表  启动hadoop并进入hiv">
<meta property="og:type" content="article">
<meta property="og:title" content="Hive高级用法">
<meta property="og:url" content="http://example.com/2020/12/18/hive%E9%AB%98%E7%BA%A7/index.html">
<meta property="og:site_name" content="Hexo">
<meta property="og:description" content="Hive高级用法 实验目的 熟悉hive QL语句实现表的各种连接方式，使用hiveserver2建立hive远程连接。  实验内容 一、表连接 1.表连接（内连接，左外连接，右外连接和全外连接）   hive只支持等值连接，即ON子句中使用等号连接，不支持非等值连接。 如果连接语句中有WHERE子句，会先执行JOIN子句，再执行WHERE子句。 可以JOIN多个表  启动hadoop并进入hiv">
<meta property="og:locale" content="en_US">
<meta property="og:image" content="c:/Users/chenhongda/AppData/Roaming/Typora/typora-user-images/1604892759358.png">
<meta property="og:image" content="c:/Users/chenhongda/AppData/Roaming/Typora/typora-user-images/1604892903366.png">
<meta property="og:image" content="c:/Users/chenhongda/AppData/Roaming/Typora/typora-user-images/1604893143606.png">
<meta property="og:image" content="c:/Users/chenhongda/AppData/Roaming/Typora/typora-user-images/1604893265259.png">
<meta property="og:image" content="c:/Users/chenhongda/AppData/Roaming/Typora/typora-user-images/1604893550556.png">
<meta property="og:image" content="c:/Users/chenhongda/AppData/Roaming/Typora/typora-user-images/1604893740149.png">
<meta property="og:image" content="c:/Users/chenhongda/AppData/Roaming/Typora/typora-user-images/1604893921784.png">
<meta property="og:image" content="c:/Users/chenhongda/AppData/Roaming/Typora/typora-user-images/1604894047611.png">
<meta property="og:image" content="c:/Users/chenhongda/AppData/Roaming/Typora/typora-user-images/1604894302464.png">
<meta property="og:image" content="c:/Users/chenhongda/AppData/Roaming/Typora/typora-user-images/1604894323731.png">
<meta property="og:image" content="c:/Users/chenhongda/AppData/Roaming/Typora/typora-user-images/1604894457933.png">
<meta property="og:image" content="c:/Users/chenhongda/AppData/Roaming/Typora/typora-user-images/1604894745677.png">
<meta property="og:image" content="c:/Users/chenhongda/AppData/Roaming/Typora/typora-user-images/1604895008985.png">
<meta property="og:image" content="c:/Users/chenhongda/AppData/Roaming/Typora/typora-user-images/1604895137140.png">
<meta property="og:image" content="c:/Users/chenhongda/AppData/Roaming/Typora/typora-user-images/1604917449601.png">
<meta property="og:image" content="c:/Users/chenhongda/AppData/Roaming/Typora/typora-user-images/1604917661571.png">
<meta property="og:image" content="c:/Users/chenhongda/AppData/Roaming/Typora/typora-user-images/1604917928517.png">
<meta property="og:image" content="c:/Users/chenhongda/AppData/Roaming/Typora/typora-user-images/1604917956138.png">
<meta property="og:image" content="c:/Users/chenhongda/AppData/Roaming/Typora/typora-user-images/1604918501621.png">
<meta property="og:image" content="c:/Users/chenhongda/AppData/Roaming/Typora/typora-user-images/1604921028362.png">
<meta property="og:image" content="c:/Users/chenhongda/AppData/Roaming/Typora/typora-user-images/1604921130241.png">
<meta property="og:image" content="c:/Users/chenhongda/AppData/Roaming/Typora/typora-user-images/1604922034791.png">
<meta property="og:image" content="c:/Users/chenhongda/AppData/Roaming/Typora/typora-user-images/1604922154628.png">
<meta property="og:image" content="c:/Users/chenhongda/AppData/Roaming/Typora/typora-user-images/1604922327579.png">
<meta property="og:image" content="c:/Users/chenhongda/AppData/Roaming/Typora/typora-user-images/1604922343534.png">
<meta property="og:image" content="c:/Users/chenhongda/AppData/Roaming/Typora/typora-user-images/1604922576329.png">
<meta property="og:image" content="c:/Users/chenhongda/AppData/Roaming/Typora/typora-user-images/1604922745535.png">
<meta property="og:image" content="c:/Users/chenhongda/AppData/Roaming/Typora/typora-user-images/1604923040320.png">
<meta property="article:published_time" content="2020-12-18T01:28:02.189Z">
<meta property="article:modified_time" content="2020-12-18T01:27:32.966Z">
<meta property="article:author" content="John Doe">
<meta name="twitter:card" content="summary">
<meta name="twitter:image" content="c:/Users/chenhongda/AppData/Roaming/Typora/typora-user-images/1604892759358.png">
  
    <link rel="alternate" href="/atom.xml" title="Hexo" type="application/atom+xml">
  
  
    <link rel="shortcut icon" href="/favicon.png">
  
  
    
<link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/typeface-source-code-pro@0.0.71/index.min.css">

  
  
<link rel="stylesheet" href="/css/style.css">

  
    
<link rel="stylesheet" href="/fancybox/jquery.fancybox.min.css">

  
<meta name="generator" content="Hexo 5.3.0"></head>

<body>
  <div id="container">
    <div id="wrap">
      <header id="header">
  <div id="banner"></div>
  <div id="header-outer" class="outer">
    <div id="header-title" class="inner">
      <h1 id="logo-wrap">
        <a href="/" id="logo">Hexo</a>
      </h1>
      
    </div>
    <div id="header-inner" class="inner">
      <nav id="main-nav">
        <a id="main-nav-toggle" class="nav-icon"></a>
        
          <a class="main-nav-link" href="/">Home</a>
        
          <a class="main-nav-link" href="/archives">Archives</a>
        
      </nav>
      <nav id="sub-nav">
        
          <a id="nav-rss-link" class="nav-icon" href="/atom.xml" title="RSS Feed"></a>
        
        <a id="nav-search-btn" class="nav-icon" title="Search"></a>
      </nav>
      <div id="search-form-wrap">
        <form action="//google.com/search" method="get" accept-charset="UTF-8" class="search-form"><input type="search" name="q" class="search-form-input" placeholder="Search"><button type="submit" class="search-form-submit">&#xF002;</button><input type="hidden" name="sitesearch" value="http://example.com"></form>
      </div>
    </div>
  </div>
</header>

      <div class="outer">
        <section id="main"><article id="post-hive高级" class="h-entry article article-type-post" itemprop="blogPost" itemscope itemtype="https://schema.org/BlogPosting">
  <div class="article-meta">
    <a href="/2020/12/18/hive%E9%AB%98%E7%BA%A7/" class="article-date">
  <time class="dt-published" datetime="2020-12-18T01:28:02.189Z" itemprop="datePublished">2020-12-18</time>
</a>
    
  </div>
  <div class="article-inner">
    
    
      <header class="article-header">
        
  
    <h1 class="p-name article-title" itemprop="headline name">
      Hive高级用法
    </h1>
  

      </header>
    
    <div class="e-content article-entry" itemprop="articleBody">
      
        <h1><center>Hive高级用法</center></h1>
<h2>实验目的</h2>
熟悉hive QL语句实现表的各种连接方式，使用hiveserver2建立hive远程连接。

<h2>实验内容</h2>
<h3>一、表连接</h3>
1.表连接（内连接，左外连接，右外连接和全外连接）

<ul>
<li>hive只支持等值连接，即ON子句中使用等号连接，不支持非等值连接。</li>
<li>如果连接语句中有WHERE子句，会先执行JOIN子句，再执行WHERE子句。</li>
<li>可以JOIN多个表</li>
</ul>
<p>启动hadoop并进入hive</p>
<figure class="highlight shell"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">start-all.sh</span><br><span class="line">hive</span><br></pre></td></tr></table></figure>
<p>建立连个表stu和score,分别从本地文件载入数据。</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">create</span> <span class="keyword">table</span> stu(<span class="keyword">id</span> <span class="built_in">int</span>, <span class="keyword">name</span> <span class="keyword">string</span>) <span class="keyword">row</span> <span class="keyword">format</span> <span class="keyword">delimited</span> <span class="keyword">fields</span> <span class="keyword">terminated</span> <span class="keyword">by</span> <span class="string">&#x27;,&#x27;</span> <span class="keyword">stored</span> <span class="keyword">as</span> textfile;</span><br><span class="line"><span class="keyword">create</span> <span class="keyword">table</span> score(s_id <span class="built_in">int</span>, score <span class="built_in">int</span>) <span class="keyword">row</span> <span class="keyword">format</span> <span class="keyword">delimited</span> <span class="keyword">fields</span> <span class="keyword">terminated</span> <span class="keyword">by</span> <span class="string">&#x27;,&#x27;</span> <span class="keyword">stored</span> <span class="keyword">as</span> textfile;</span><br></pre></td></tr></table></figure>
<p><img src="C:\Users\chenhongda\AppData\Roaming\Typora\typora-user-images\1604892759358.png" alt="1604892759358"></p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">load</span> <span class="keyword">data</span> <span class="keyword">local</span> inpath <span class="string">&#x27;/data/stu.csv&#x27;</span> <span class="keyword">into</span> <span class="keyword">table</span> stu;</span><br><span class="line"><span class="keyword">load</span> <span class="keyword">data</span> <span class="keyword">local</span> inpath <span class="string">&#x27;/data/score.csv&#x27;</span> <span class="keyword">into</span> <span class="keyword">table</span> score;</span><br></pre></td></tr></table></figure>
<p>表中数据</p>
<p><img src="C:\Users\chenhongda\AppData\Roaming\Typora\typora-user-images\1604892903366.png" alt="1604892903366"></p>
<p>内连接（两个表的交集）</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> * <span class="keyword">from</span> stu <span class="keyword">join</span> score <span class="keyword">on</span> stu.id=score.s_id;</span><br></pre></td></tr></table></figure>
<p><img src="C:\Users\chenhongda\AppData\Roaming\Typora\typora-user-images\1604893143606.png" alt="1604893143606"></p>
<p>左外连接（以左表为准显示，右表没有对应的补NULL）left outer</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> * <span class="keyword">from</span> stu <span class="keyword">left</span> <span class="keyword">outer</span> <span class="keyword">join</span> score <span class="keyword">on</span> stu.id=score.s_id;</span><br></pre></td></tr></table></figure>
<p><img src="C:\Users\chenhongda\AppData\Roaming\Typora\typora-user-images\1604893265259.png" alt="1604893265259"></p>
<p>右连接（以右表为准，左表没有对应的补NULL） right outer</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> * <span class="keyword">from</span> stu <span class="keyword">right</span> <span class="keyword">outer</span> <span class="keyword">join</span> score <span class="keyword">on</span> stu.id=score.s_id;</span><br></pre></td></tr></table></figure>
<p><img src="C:\Users\chenhongda\AppData\Roaming\Typora\typora-user-images\1604893550556.png" alt="1604893550556"></p>
<p>全外连接（两个表的并集）full outer</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> * <span class="keyword">from</span> stu <span class="keyword">full</span> <span class="keyword">outer</span> <span class="keyword">join</span> score <span class="keyword">on</span> stu.id=score.s_id;</span><br></pre></td></tr></table></figure>
<p><img src="C:\Users\chenhongda\AppData\Roaming\Typora\typora-user-images\1604893740149.png" alt="1604893740149"></p>
<p>左半连接（相当于内连接后去掉右表数据）left semi</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> * <span class="keyword">from</span> stu <span class="keyword">left</span> <span class="keyword">semi</span> <span class="keyword">join</span> score <span class="keyword">on</span> stu.id=score.s_id;</span><br></pre></td></tr></table></figure>
<p><img src="C:\Users\chenhongda\AppData\Roaming\Typora\typora-user-images\1604893921784.png" alt="1604893921784"></p>
<p>相当于下面 in，而且这条执行的更快。1.59s</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> * <span class="keyword">from</span> stu <span class="keyword">where</span> <span class="keyword">id</span> <span class="keyword">in</span> (<span class="keyword">select</span> s_id <span class="keyword">from</span> score);</span><br></pre></td></tr></table></figure>
<p><img src="C:\Users\chenhongda\AppData\Roaming\Typora\typora-user-images\1604894047611.png" alt="1604894047611"></p>
<h3>二、Hive的连接</h3>
**CLI连接**

<p>在命令行直接执行hive相当于执行hive –service cli;</p>
<p>使用hive –help可以看hive命令能够启动哪些服务；</p>
<p>通过 hive –service serviceName –help  可以查看某个具体命令的使用方式。</p>
<p><img src="C:\Users\chenhongda\AppData\Roaming\Typora\typora-user-images\1604894302464.png" alt="1604894302464"></p>
<p><img src="C:\Users\chenhongda\AppData\Roaming\Typora\typora-user-images\1604894323731.png" alt="1604894323731"></p>
<p><strong>HiveServer2</strong></p>
<p>HiveServer 允许远程客户端可以使用各种编程语言向 Hive 提交请求并检索结果。 HiveServer2 是 HiveServer 改进版本，提供了新的 ThriftAPI 来处理 JDBC 或者 ODBC 客 户端，可以进行 Kerberos 身份验证，支持多个客户端并发。 </p>
<p><strong>Beeline</strong></p>
<p>HiveServer2 提供了新的 CLI：BeeLine，它是基于 SQLLine CLI 的 JDBC 客户端。Hive CLI 与 Beeline 均为控制台命令行操作模式，区别在于 Hive CLI 只能操作本地 Hive 服务， 而 Beeline 可以通过 JDBC 连接远程服 。</p>
<p>Beeline 使用密码登录 Hive，需要设置用户名和密码，在 hive-site.xml 4489 行和 4494 行做如下修改，将两处的值 anonymous，改为自己的用户名和密码。  </p>
<p>/apps/hive/conf/hive-site.xml</p>
<p><img src="C:\Users\chenhongda\AppData\Roaming\Typora\typora-user-images\1604894457933.png" alt="1604894457933"></p>
<p>在/apps/hadoop/etc/hadoop/core-site.xml中添加配置</p>
<ul>
<li>hadoop.proxyuser.chen.hosts 表示代理用户chen的所属组在任意节点都能访问 HDFS 集群 </li>
<li>hadoop.proxyuser.chen.groups 表示任意节点使用 Hadoop 集群的代理用户chen都能访问 HDFS 集群 </li>
</ul>
<p><img src="C:\Users\chenhongda\AppData\Roaming\Typora\typora-user-images\1604894745677.png" alt="1604894745677"></p>
<p>在做完以上配置后，hadoop需要重启。</p>
<figure class="highlight shell"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">stop-all.sh</span><br><span class="line">start-all.sh</span><br></pre></td></tr></table></figure>
<p>后台启动hiveserver2</p>
<figure class="highlight shell"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">hiveserver2 &amp;</span><br></pre></td></tr></table></figure>
<p>等待几秒钟后，可以看到RunJar这个进程。</p>
<p><img src="C:\Users\chenhongda\AppData\Roaming\Typora\typora-user-images\1604895008985.png" alt="1604895008985"></p>
<p>另一个终端</p>
<p>第一种连接方式：</p>
<p><img src="C:\Users\chenhongda\AppData\Roaming\Typora\typora-user-images\1604895137140.png" alt="1604895137140"></p>
<p>第二种连接方式：</p>
<p>直接在命令行执行</p>
<ul>
<li>-u 指定元数据库的链接信息</li>
<li>-n 指定用户名</li>
<li>-p 指定密码</li>
</ul>
<figure class="highlight shell"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">beeline -u jdbc:hive2://ubuntu:10000/default -n chen -p 123456</span><br></pre></td></tr></table></figure>
<p><img src="C:\Users\chenhongda\AppData\Roaming\Typora\typora-user-images\1604917449601.png" alt="1604917449601"></p>
<p>远程登录成功</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">show</span> <span class="keyword">databases</span>;</span><br></pre></td></tr></table></figure>
<p><img src="C:\Users\chenhongda\AppData\Roaming\Typora\typora-user-images\1604917661571.png" alt="1604917661571"></p>
<p><strong>Beeline管理命令</strong></p>
<p>beeline执行查询都是正常的SQL输入，管理命令，比如进行连接，中断，退出，命令前需要加!不需要终止符。</p>
<ul>
<li>!connect url     连接不同的hiveserver2服务器</li>
<li>!exit                 退出shell</li>
<li>!help                显示全部管理命令</li>
</ul>
<p><img src="C:\Users\chenhongda\AppData\Roaming\Typora\typora-user-images\1604917928517.png" alt="1604917928517"></p>
<p><img src="C:\Users\chenhongda\AppData\Roaming\Typora\typora-user-images\1604917956138.png" alt="1604917956138"></p>
<p><strong>hiveserver2 webUI 界面</strong></p>
<p><img src="C:\Users\chenhongda\AppData\Roaming\Typora\typora-user-images\1604918501621.png" alt="1604918501621"></p>
<h3>三、Hive JDBC</h3>
类似于java访问关系型数据库，主要是URL和驱动不一样，而且主要是查询数据，不可以删除和更新数据。

<p>hive JDBC 的连接参数如下：</p>
<p>驱动名：org.apache.hive.jdbc.HiveDriver</p>
<p>连接字符串：jdbc:hive2://主机:端口/数据库名（默认数据库是default）</p>
<p>HiveService.java</p>
<figure class="highlight java"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br><span class="line">41</span><br><span class="line">42</span><br><span class="line">43</span><br><span class="line">44</span><br><span class="line">45</span><br><span class="line">46</span><br><span class="line">47</span><br><span class="line">48</span><br><span class="line">49</span><br><span class="line">50</span><br><span class="line">51</span><br><span class="line">52</span><br><span class="line">53</span><br><span class="line">54</span><br><span class="line">55</span><br><span class="line">56</span><br><span class="line">57</span><br><span class="line">58</span><br><span class="line">59</span><br><span class="line">60</span><br><span class="line">61</span><br><span class="line">62</span><br><span class="line">63</span><br><span class="line">64</span><br><span class="line">65</span><br><span class="line">66</span><br><span class="line">67</span><br><span class="line">68</span><br><span class="line">69</span><br><span class="line">70</span><br><span class="line">71</span><br><span class="line">72</span><br><span class="line">73</span><br><span class="line">74</span><br><span class="line">75</span><br><span class="line">76</span><br><span class="line">77</span><br><span class="line">78</span><br><span class="line">79</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">package</span> dsd.hive;</span><br><span class="line"></span><br><span class="line"><span class="keyword">import</span> java.sql.Connection;</span><br><span class="line"><span class="keyword">import</span> java.sql.DriverManager;</span><br><span class="line"><span class="keyword">import</span> java.sql.SQLException;</span><br><span class="line"><span class="keyword">import</span> java.sql.Statement;</span><br><span class="line"><span class="keyword">import</span> org.apache.log4j.Logger;</span><br><span class="line"></span><br><span class="line"><span class="keyword">public</span> <span class="class"><span class="keyword">class</span> <span class="title">HiveService</span> </span>&#123;</span><br><span class="line">	<span class="keyword">static</span> Logger logger = Logger.getLogger(HiveService.class);</span><br><span class="line">	<span class="comment">// hive的jdbc驱动类</span></span><br><span class="line">	<span class="keyword">public</span> <span class="keyword">static</span> String dirverName = <span class="string">&quot;org.apache.hive.jdbc.HiveDriver&quot;</span>;</span><br><span class="line">	<span class="comment">// 连接hive的URL hive2版本需要的是jdbc:hive2，而不是 jdbc:hive</span></span><br><span class="line">	<span class="keyword">public</span> <span class="keyword">static</span> String url = <span class="string">&quot;jdbc:hive2://localhost:10000&quot;</span>;</span><br><span class="line">	<span class="comment">// 登录linux的用户名 一般会给权限大一点的用户，否则无法进行事务型操作</span></span><br><span class="line">	<span class="keyword">public</span> <span class="keyword">static</span> String user = <span class="string">&quot;chen&quot;</span>;</span><br><span class="line">	<span class="comment">// 登录linux的密码</span></span><br><span class="line">	<span class="keyword">public</span> <span class="keyword">static</span> String pass = <span class="string">&quot;123456&quot;</span>;</span><br><span class="line"></span><br><span class="line">	<span class="comment">/**</span></span><br><span class="line"><span class="comment">	 * 创建连接</span></span><br><span class="line"><span class="comment">	 * </span></span><br><span class="line"><span class="comment">	 * <span class="doctag">@return</span></span></span><br><span class="line"><span class="comment">	 * <span class="doctag">@throws</span> SQLException</span></span><br><span class="line"><span class="comment">	 */</span></span><br><span class="line">	<span class="function"><span class="keyword">public</span> <span class="keyword">static</span> Connection <span class="title">getConn</span><span class="params">()</span> </span>&#123;</span><br><span class="line">		Connection conn = <span class="keyword">null</span>;</span><br><span class="line">		<span class="keyword">try</span> &#123;</span><br><span class="line">			Class.forName(dirverName);</span><br><span class="line">			conn = DriverManager.getConnection(url, user, pass);</span><br><span class="line">		&#125; <span class="keyword">catch</span> (ClassNotFoundException e) &#123;</span><br><span class="line">			e.printStackTrace();</span><br><span class="line">		&#125; <span class="keyword">catch</span> (SQLException e) &#123;</span><br><span class="line">			e.printStackTrace();</span><br><span class="line">		&#125;</span><br><span class="line">		<span class="keyword">return</span> conn;</span><br><span class="line">	&#125;</span><br><span class="line"></span><br><span class="line">	<span class="comment">/**</span></span><br><span class="line"><span class="comment">	 * 创建命令</span></span><br><span class="line"><span class="comment">	 * </span></span><br><span class="line"><span class="comment">	 * <span class="doctag">@param</span> conn</span></span><br><span class="line"><span class="comment">	 * <span class="doctag">@return</span></span></span><br><span class="line"><span class="comment">	 * <span class="doctag">@throws</span> SQLException</span></span><br><span class="line"><span class="comment">	 */</span></span><br><span class="line">	<span class="function"><span class="keyword">public</span> <span class="keyword">static</span> Statement <span class="title">getStmt</span><span class="params">(Connection conn)</span> <span class="keyword">throws</span> SQLException </span>&#123;</span><br><span class="line">		logger.debug(conn);</span><br><span class="line">		<span class="keyword">if</span> (conn == <span class="keyword">null</span>) &#123;</span><br><span class="line">			logger.debug(<span class="string">&quot;this conn is null&quot;</span>);</span><br><span class="line">		&#125;</span><br><span class="line">		<span class="keyword">return</span> conn.createStatement();</span><br><span class="line">	&#125;</span><br><span class="line"></span><br><span class="line">	<span class="comment">/**</span></span><br><span class="line"><span class="comment">	 * 关闭连接</span></span><br><span class="line"><span class="comment">	 * </span></span><br><span class="line"><span class="comment">	 * <span class="doctag">@param</span> conn</span></span><br><span class="line"><span class="comment">	 */</span></span><br><span class="line">	<span class="function"><span class="keyword">public</span> <span class="keyword">static</span> <span class="keyword">void</span> <span class="title">closeConn</span><span class="params">(Connection conn)</span> </span>&#123;</span><br><span class="line">		<span class="keyword">try</span> &#123;</span><br><span class="line">			conn.close();</span><br><span class="line">		&#125; <span class="keyword">catch</span> (SQLException e) &#123;</span><br><span class="line">			e.printStackTrace();</span><br><span class="line">		&#125;</span><br><span class="line">	&#125;</span><br><span class="line"></span><br><span class="line">	<span class="comment">/**</span></span><br><span class="line"><span class="comment">	 * 关闭命令</span></span><br><span class="line"><span class="comment">	 * </span></span><br><span class="line"><span class="comment">	 * <span class="doctag">@param</span> stmt</span></span><br><span class="line"><span class="comment">	 */</span></span><br><span class="line">	<span class="function"><span class="keyword">public</span> <span class="keyword">static</span> <span class="keyword">void</span> <span class="title">closeStmt</span><span class="params">(Statement stmt)</span> </span>&#123;</span><br><span class="line">		<span class="keyword">try</span> &#123;</span><br><span class="line">			stmt.close();</span><br><span class="line">		&#125; <span class="keyword">catch</span> (SQLException e) &#123;</span><br><span class="line">			e.printStackTrace();</span><br><span class="line">		&#125;</span><br><span class="line">	&#125;</span><br><span class="line">&#125;</span><br></pre></td></tr></table></figure>
<p>HiveTest.java</p>
<figure class="highlight java"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br><span class="line">41</span><br><span class="line">42</span><br><span class="line">43</span><br><span class="line">44</span><br><span class="line">45</span><br><span class="line">46</span><br><span class="line">47</span><br><span class="line">48</span><br><span class="line">49</span><br><span class="line">50</span><br><span class="line">51</span><br><span class="line">52</span><br><span class="line">53</span><br><span class="line">54</span><br><span class="line">55</span><br><span class="line">56</span><br><span class="line">57</span><br><span class="line">58</span><br><span class="line">59</span><br><span class="line">60</span><br><span class="line">61</span><br><span class="line">62</span><br><span class="line">63</span><br><span class="line">64</span><br><span class="line">65</span><br><span class="line">66</span><br><span class="line">67</span><br><span class="line">68</span><br><span class="line">69</span><br><span class="line">70</span><br><span class="line">71</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">package</span> dsd.hive;</span><br><span class="line"></span><br><span class="line"><span class="keyword">import</span> java.sql.Connection;</span><br><span class="line"><span class="keyword">import</span> java.sql.ResultSet;</span><br><span class="line"><span class="keyword">import</span> java.sql.ResultSetMetaData;</span><br><span class="line"><span class="keyword">import</span> java.sql.SQLException;</span><br><span class="line"><span class="keyword">import</span> java.sql.Statement;</span><br><span class="line"><span class="keyword">import</span> org.apache.log4j.Logger;</span><br><span class="line"></span><br><span class="line"><span class="keyword">public</span> <span class="class"><span class="keyword">class</span> <span class="title">HiveTest</span> </span>&#123;</span><br><span class="line"></span><br><span class="line">	<span class="keyword">static</span> Logger logger = Logger.getLogger(HiveTest.class);</span><br><span class="line"></span><br><span class="line">	<span class="function"><span class="keyword">public</span> <span class="keyword">static</span> <span class="keyword">void</span> <span class="title">main</span><span class="params">(String[] args)</span> </span>&#123;</span><br><span class="line"></span><br><span class="line">		Connection conn = HiveService.getConn();</span><br><span class="line">		Statement stmt = <span class="keyword">null</span>;</span><br><span class="line">		<span class="keyword">try</span> &#123;</span><br><span class="line"></span><br><span class="line">			stmt = HiveService.getStmt(conn);</span><br><span class="line">			stmt.execute(<span class="string">&quot;drop table if exists users&quot;</span>);<span class="comment">// 需要拥有hdfs文件读写权限的用户才可以进行此操作</span></span><br><span class="line">			logger.debug(<span class="string">&quot;drop table is susscessful&quot;</span>);</span><br><span class="line"></span><br><span class="line">			stmt.execute(<span class="string">&quot;create table users(user_id int, fname string,lname string )  row format delimited fields terminated by &#x27;,&#x27;&quot;</span>);<span class="comment">// 需要拥有hdfs文件读写权限的用户才可以进行此操作</span></span><br><span class="line">			logger.debug(<span class="string">&quot;create table is susscessful&quot;</span>);</span><br><span class="line"></span><br><span class="line">			stmt.execute(<span class="string">&quot;insert into users(user_id, fname,lname) values(100,&#x27;hongda&#x27;,&#x27;chen&#x27;)&quot;</span>);<span class="comment">// 需要拥有hdfs文件读写权限的用户才可以进行此操作</span></span><br><span class="line">			logger.debug(<span class="string">&quot;insert is susscessful&quot;</span>);</span><br><span class="line"></span><br><span class="line">			stmt.execute(<span class="string">&quot;load data local inpath &#x27;/home/chen/hive_higher/user.txt&#x27; overwrite into table users&quot;</span>);<span class="comment">// 需要拥有hdfs文件读写权限的用户才可以进行此操作</span></span><br><span class="line">			logger.debug(<span class="string">&quot;load data is susscessful&quot;</span>);</span><br><span class="line"></span><br><span class="line">			String sql = <span class="string">&quot;select * from users&quot;</span>;</span><br><span class="line"></span><br><span class="line">			ResultSet res = <span class="keyword">null</span>;</span><br><span class="line">			res = stmt.executeQuery(sql);</span><br><span class="line"></span><br><span class="line">			ResultSetMetaData meta = res.getMetaData();   <span class="comment">//fields name</span></span><br><span class="line"></span><br><span class="line">			<span class="keyword">for</span> (<span class="keyword">int</span> i = <span class="number">1</span>; i &lt;= meta.getColumnCount(); i++) &#123;</span><br><span class="line">				System.out.print(meta.getColumnName(i) + <span class="string">&quot;\t&quot;</span>);</span><br><span class="line">			&#125;</span><br><span class="line">			System.out.println();</span><br><span class="line">			<span class="keyword">while</span> (res.next()) &#123;</span><br><span class="line">				System.out.print(res.getInt(<span class="number">1</span>) + <span class="string">&quot;\t\t&quot;</span>);</span><br><span class="line">				System.out.print(res.getString(<span class="number">2</span>) + <span class="string">&quot;\t\t&quot;</span>);</span><br><span class="line">				System.out.print(res.getString(<span class="number">3</span>));</span><br><span class="line">				System.out.println();</span><br><span class="line">			&#125;</span><br><span class="line"></span><br><span class="line">			sql = <span class="string">&quot;show tables &quot;</span>;</span><br><span class="line">			System.out.println(<span class="string">&quot;\nRunning: &quot;</span> + sql);</span><br><span class="line">			res = stmt.executeQuery(sql);</span><br><span class="line">			<span class="keyword">while</span> (res.next()) &#123;</span><br><span class="line">				System.out.println(res.getString(<span class="number">1</span>));</span><br><span class="line">			&#125;</span><br><span class="line">			<span class="comment">// describe table</span></span><br><span class="line">			sql = <span class="string">&quot;describe users&quot;</span>;</span><br><span class="line">			System.out.println(<span class="string">&quot;\nRunning: &quot;</span> + sql);</span><br><span class="line">			res = stmt.executeQuery(sql);</span><br><span class="line">			<span class="keyword">while</span> (res.next()) &#123;</span><br><span class="line">				System.out.println(res.getString(<span class="number">1</span>) + <span class="string">&quot;\t&quot;</span> + res.getString(<span class="number">2</span>));</span><br><span class="line">			&#125;</span><br><span class="line"></span><br><span class="line">		&#125; <span class="keyword">catch</span> (SQLException e) &#123;</span><br><span class="line">			e.printStackTrace();</span><br><span class="line">		&#125;</span><br><span class="line">		HiveService.closeStmt(stmt);</span><br><span class="line">		HiveService.closeConn(conn);</span><br><span class="line">	&#125;</span><br><span class="line">&#125;</span><br></pre></td></tr></table></figure>
<p>建立hive_api项目。导入/apps/hive/lib下的所有jar包。启动hiveserver2</p>
<p>运行HiveTest.java       run as javaApplication</p>
<p><img src="C:\Users\chenhongda\AppData\Roaming\Typora\typora-user-images\1604921028362.png" alt="1604921028362"></p>
<p>eclipse闪退的问题</p>
<p>在/apps/eclipse/eclipse.ini修改23行为512</p>
<p><img src="C:\Users\chenhongda\AppData\Roaming\Typora\typora-user-images\1604921130241.png" alt="1604921130241"></p>
<h3>四、Hive UDF（user-defined functions）</h3>
注意

<ul>
<li>自定义UDF需要继承org.apache.hadoop.hive.ql.exec.UDF</li>
<li>需要实现evaluate函数</li>
<li>evaluate 函数支持重载</li>
</ul>
<p>新建工程【hive_udf】并创建包【sds.hive_udf】和类【StringExt】。将 Hive 安装目录下的 lib 文件夹中的全部 Jar 文件作为外部 Jar 文件导入。 </p>
<p>StringExt.java</p>
<figure class="highlight java"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">package</span> sds.hive_udf;</span><br><span class="line"></span><br><span class="line"><span class="keyword">import</span> org.apache.hadoop.hive.ql.exec.UDF;</span><br><span class="line"></span><br><span class="line"><span class="comment">/**</span></span><br><span class="line"><span class="comment"> * 自定义Hive函数，需要继承org.apache.hadoop.hive.ql.exec.UDF 并实现evaluate方法</span></span><br><span class="line"><span class="comment"> * </span></span><br><span class="line"><span class="comment"> */</span></span><br><span class="line"><span class="keyword">public</span> <span class="class"><span class="keyword">class</span> <span class="title">StringExt</span> <span class="keyword">extends</span> <span class="title">UDF</span> </span>&#123;</span><br><span class="line"></span><br><span class="line">	<span class="function"><span class="keyword">public</span> String <span class="title">evaluate</span><span class="params">(String name)</span> </span>&#123;</span><br><span class="line">		<span class="keyword">return</span> <span class="string">&quot;Hello &quot;</span> + name;</span><br><span class="line">	&#125;</span><br><span class="line"></span><br><span class="line">	<span class="comment">// 添加一个空的main方法是为了使用eclipse工具打成jar包时方便；</span></span><br><span class="line">	<span class="comment">// 如果没有main方法，不能使用eclipse工具可视化打成jar包</span></span><br><span class="line">	<span class="function"><span class="keyword">public</span> <span class="keyword">static</span> <span class="keyword">void</span> <span class="title">main</span><span class="params">(String[] args)</span> </span>&#123;</span><br><span class="line"></span><br><span class="line">	&#125;</span><br><span class="line">&#125;</span><br></pre></td></tr></table></figure>
<p><img src="C:\Users\chenhongda\AppData\Roaming\Typora\typora-user-images\1604922034791.png"></p>
<p>使用 add 命令将 Jar 包导入到 Hive 的 ClassPath，如果 jar 包 myudf.jar 放在 $HIVE_HOME/lib 目录下，这一步可以省略。 </p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">add jar /home/chen/project_jar/myudf.jar</span><br></pre></td></tr></table></figure>
<p><img src="C:\Users\chenhongda\AppData\Roaming\Typora\typora-user-images\1604922154628.png" alt="1604922154628"></p>
<p>创建临时函数</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">create</span> <span class="keyword">temporary</span> <span class="keyword">function</span> stringext <span class="keyword">as</span> <span class="string">&#x27;sds.hive_udf.StringExt&#x27;</span>;</span><br></pre></td></tr></table></figure>
<p><img src="C:\Users\chenhongda\AppData\Roaming\Typora\typora-user-images\1604922327579.png" alt="1604922327579"></p>
<p>执行结果</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> fname,stringext(lname) <span class="keyword">from</span> <span class="keyword">users</span>;</span><br></pre></td></tr></table></figure>
<p><img src="C:\Users\chenhongda\AppData\Roaming\Typora\typora-user-images\1604922343534.png" alt="1604922343534"></p>
<p>临时函数在hive退出后就会自动失效，永久函数的创建方法时将jar包上传Hdfs。</p>
<p><img src="C:\Users\chenhongda\AppData\Roaming\Typora\typora-user-images\1604922576329.png" alt="1604922576329"></p>
<p>创建永久函数</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">create</span> <span class="keyword">function</span> stringext <span class="keyword">as</span> <span class="string">&#x27;sds.hive_udf.StringExt&#x27;</span> <span class="keyword">using</span> jar <span class="string">&#x27;hdfs:///lib/myudf.jar&#x27;</span>;</span><br></pre></td></tr></table></figure>
<p><img src="C:\Users\chenhongda\AppData\Roaming\Typora\typora-user-images\1604922745535.png" alt="1604922745535"></p>
<p>打开Mysql</p>
<p>查看FUNCS</p>
<p><img src="C:\Users\chenhongda\AppData\Roaming\Typora\typora-user-images\1604923040320.png" alt="1604923040320"></p>
<p> 可以看到函数 stringext 的信息已经注册。 </p>

      
    </div>
    <footer class="article-footer">
      <a data-url="http://example.com/2020/12/18/hive%E9%AB%98%E7%BA%A7/" data-id="ckitlg0su00024cn64tbb1oqw" data-title="Hive高级用法" class="article-share-link">Share</a>
      
      
      
    </footer>
  </div>
  
    
<nav id="article-nav">
  
  
    <a href="/2020/12/18/Hadoop%E7%A8%8B%E5%BA%8F%E5%BC%80%E5%8F%91/" id="article-nav-older" class="article-nav-link-wrap">
      <strong class="article-nav-caption">Older</strong>
      <div class="article-nav-title">Hadoop ���򿪷�</div>
    </a>
  
</nav>

  
</article>


</section>
        
          <aside id="sidebar">
  
    

  
    

  
    
  
    
  <div class="widget-wrap">
    <h3 class="widget-title">Archives</h3>
    <div class="widget">
      <ul class="archive-list"><li class="archive-list-item"><a class="archive-list-link" href="/archives/2020/12/">December 2020</a></li></ul>
    </div>
  </div>


  
    
  <div class="widget-wrap">
    <h3 class="widget-title">Recent Posts</h3>
    <div class="widget">
      <ul>
        
          <li>
            <a href="/2020/12/18/hive%E9%AB%98%E7%BA%A7/">Hive高级用法</a>
          </li>
        
          <li>
            <a href="/2020/12/18/Hadoop%E7%A8%8B%E5%BA%8F%E5%BC%80%E5%8F%91/">Hadoop ���򿪷�</a>
          </li>
        
          <li>
            <a href="/2020/12/18/spark%E5%AE%89%E8%A3%85/">Spark</a>
          </li>
        
          <li>
            <a href="/2020/12/18/%E6%88%91%E7%9A%84%E7%AC%AC%E4%B8%80%E7%AF%87%E5%8D%9A%E5%AE%A2/">我的第一篇博客</a>
          </li>
        
          <li>
            <a href="/2020/12/18/hello-world/">Hello World</a>
          </li>
        
      </ul>
    </div>
  </div>

  
</aside>
        
      </div>
      <footer id="footer">
  
  <div class="outer">
    <div id="footer-info" class="inner">
      
      &copy; 2020 John Doe<br>
      Powered by <a href="https://hexo.io/" target="_blank">Hexo</a>
    </div>
  </div>
</footer>

    </div>
    <nav id="mobile-nav">
  
    <a href="/" class="mobile-nav-link">Home</a>
  
    <a href="/archives" class="mobile-nav-link">Archives</a>
  
</nav>
    


<script src="/js/jquery-3.4.1.min.js"></script>



  
<script src="/fancybox/jquery.fancybox.min.js"></script>




<script src="/js/script.js"></script>





  </div>
</body>
</html>